package dbhelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Timer;

import entities.Item;

public class Items {
	
	public static final String tableName = 
			" ebaydb.items ";
	private static final String isForSaleItemStringQuery =
			"SELECT count(*) FROM" + tableName + "WHERE id=? AND sell_time > NOW()";
	private static final String absSelectItemQuery =
			"SELECT * From" + tableName + " WHERE id=?";
	private static final String getSellerIDSQLQuery=
			absSelectItemQuery.replace("*", "seller_id");
	private static final String getCurrentBidQuery=
			absSelectItemQuery.replace("*", "current_bid");
	private static final String getWinnerIDQuery=
			absSelectItemQuery.replace("*", "winner_user_id");
	
	private static final String getNumberItemsForSaleQuery =
			"SELECT COUNT(*) FROM"+ tableName + " WHERE sell_time > NOW()";
	private static final String getNumberSoldItemsQuery=
			getNumberItemsForSaleQuery.replace(">", "<");
	private static final String recentItemsQuery = 
			"SELECT * FROM" + tableName + "LEFT OUTER JOIN" + Users.tableName + 
			"ON users.id = items.winner_user_id   " +
					"ORDER BY publish_time DESC LIMIT ?";
	/**
	 *  similar to recentItemsQuery bid we inject one more condition in where clause 
	 */
	private static final String recentItemsStartFromTimeQuery = 
			recentItemsQuery.replace("   "," WHERE publish_time > ? ");
	private static final String postItemQuery = 
			"INSERT INTO" + tableName + "(item_name, description, current_bid, picture_url," +
			" sell_time, publish_time, max_bid, seller_id)" + "VALUES (?,?,?,?,?,?,?,?)";
	
	// search strings
	
	private static final String absSearchSoldItemsQuery = 
			"(SELECT * FROM" + tableName + "," + Users.tableName + "WHERE sell_time > now()" + 
	" AND users.id=items.winner_user_id AND   (0>1";
	private static final String absSearchForSaleItemsQuery=
			absSearchSoldItemsQuery.replaceFirst(">","<");
	private static final String timeStartSearchSoldItemsQuery=
			absSearchSoldItemsQuery.replaceFirst("   ", " publish_time > ? AND ");
	private static final String timeStartSearchForSaleItemsQuery=
			absSearchForSaleItemsQuery.replaceFirst("   ", " publish_time > ? AND ");
	private static final String searchKeyWord = 
			" OR item_name LIKE ? OR description LIKE ? ";
	private static final String absOrderByPrice=
			") ORDER BY current_bid ";
	private static final String orderByPriceASC =
			absOrderByPrice +  "ASC)";
	private static final String orderByPriceDESC =
			absOrderByPrice + "DESC)";
	private static final String union = 
			" UNION ALL ";
	private static final String limit =
			" LIMIT ?,?";
	private static final int ITEMS_PER_PAGE = 20;
	/**
	 * 2 queries are being unioned
	 */  
	private static final int UNIONS_NUMBER = 2;
	/**
	 * each key word is duplicated into our keyword search string
	 */
	private static final int DUPLICATE_STRING_NUMBER = 2;
	
	
	
/**
 * @return the current available number of items for sale
 */
public static long getNumberOfItemsForSale(){
	return getNumberOfItems(getNumberItemsForSaleQuery);
}

/**
 * @return the current number of the sold items
 */
public static long getNumberOfSoldItems(){
	return getNumberOfItems(getNumberSoldItemsQuery);
}


private static long getNumberOfItems(String query){
	 PreparedStatement itemsNumberStatment = null;
	 ResultSet rs = null;
	 Connection connection = null;
	try {
		connection = MyDBConnectionPool.getConnection();
		itemsNumberStatment = connection.prepareStatement(query);
		rs = itemsNumberStatment.executeQuery();
		return rs.first() ? rs.getLong(1) : 0;
	} catch (SQLException e) {
		e.printStackTrace();
	}finally{
		try {
			if(itemsNumberStatment != null)
				itemsNumberStatment.close();
			if(rs != null)
				rs.close();
			if(connection != null)
				connection.close();
		} catch (SQLException e) {
		e.printStackTrace();
		}
	}
	return 0;
}

public static List<Item> getNewItems(int itemsNumber,java.sql.Timestamp startFrom){
	List<Item> result = new ArrayList<Item>();
	 PreparedStatement newItemsPS = null;
	 String query = (startFrom == null) ? recentItemsQuery : recentItemsStartFromTimeQuery;
	 Connection connection = null;
	try {
		connection = MyDBConnectionPool.getConnection();
		newItemsPS = connection.prepareStatement(query);
		int injectedParametersCounter = 1;
		if(startFrom != null) newItemsPS.setTimestamp(injectedParametersCounter++, startFrom);
		newItemsPS.setInt(injectedParametersCounter++, itemsNumber);

		populateItems(
				result,newItemsPS.executeQuery(),true
				);
		
		return result;
	} catch (SQLException e) {
		e.printStackTrace();
	}finally{
		try {
			if(newItemsPS != null)
				newItemsPS.close();
			if(connection != null)
				connection.close();
		} catch (SQLException e) {
		e.printStackTrace();
		}
	}
	return null;
}


private static void populateItems(List<Item> items,ResultSet rs,
		boolean withWinnerUserID){
	
	try {
		while(rs.next()){
			Item item = new Item();
			item.setID(rs.getLong("id"));
			item.setName(rs.getString("item_name"));
			item.setDescription(rs.getString("description"));
			item.setCurrentBidAmount(rs.getDouble("current_bid"));
			if(withWinnerUserID) item.setWinnerUserName(rs.getString("user_name"));
			item.setPictureURL(rs.getString("picture_url"));
			item.setSellTime(rs.getTimestamp("sell_time"));
			item.setPublishTime(rs.getTimestamp("publish_time"));
			item.setSellerID(rs.getLong("seller_id"));
			items.add(item);
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}finally{
		try {
			if(rs != null)
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}
	
}

/**
 * puts the new item on the DB 
 * @param item
 */
public static boolean postNewItem(Item item){
	 PreparedStatement registerUserPS = null;
	 Connection connection = null;
		try {
			connection = MyDBConnectionPool.getConnection();
			registerUserPS = connection.prepareStatement(postItemQuery);
			registerUserPS.setString(1, item.getName());
			registerUserPS.setString(2, item.getDescription());
			registerUserPS.setDouble(3, item.getCurrentBidAmount());
			registerUserPS.setString(4, item.getPictureURL());
			registerUserPS.setTimestamp(5, item.getSellTime());
			registerUserPS.setTimestamp(6, item.getPublishTime());
			registerUserPS.setDouble(7, item.getCurrentBidAmount());
			registerUserPS.setLong(8, item.getSellerID());

			 if(registerUserPS.executeUpdate() == 1){
				return true;
			 }
		} catch (SQLException | NullPointerException e) {
			e.printStackTrace();
		}finally{
			try {
				if(registerUserPS != null)
				registerUserPS.close();
				if(connection != null)
					connection.close();
			} catch (SQLException e) {
			e.printStackTrace();
			}
		}
		return false;
		
}


public static List<Item> searchItems(List<String> searchKeyWords,
		Timestamp startFrom, int pageNumber){
	List<Item> result = new ArrayList<Item>();
	 PreparedStatement searchItemsPS = null;
	 Connection connection = null;
	try {
		connection = MyDBConnectionPool.getConnection();
		searchItemsPS = connection.prepareStatement(
				produceQuery(searchKeyWords,startFrom)
				);
		injectKeyWords(
				searchKeyWords,searchItemsPS,startFrom,pageNumber
				);
		populateItems(
				result,searchItemsPS.executeQuery(),true
				);
		
		return result;
	} catch (SQLException e) {
		e.printStackTrace();
	}finally{
		try {
			if(searchItemsPS != null)
				searchItemsPS.close();
			if(connection != null)
				connection.close();
		} catch (SQLException e) {
		e.printStackTrace();
		}
	}
	return null;
}

//produces the long query : select..from..where..(LIKE ?)* 
// like is repeated for each key word
private static String produceQuery(List<String> searchKeyWords, 
		Timestamp startFrom){
	StringBuilder searchWordsQueryStringBuilder = new StringBuilder();
	StringBuilder resultStringBuilder = new StringBuilder();
	//produce the long appended query for each key word
	for(int i = 0;i < searchKeyWords.size(); i++){
	searchWordsQueryStringBuilder.append(searchKeyWord);
	}
	//first query (items for sale)
	appendStartingSubQuery(resultStringBuilder, startFrom,true);
	resultStringBuilder.append(searchWordsQueryStringBuilder);
	resultStringBuilder.append(Items.orderByPriceASC);
	//second query (sold items)
	resultStringBuilder.append(union);
	appendStartingSubQuery(resultStringBuilder, startFrom,false);
	resultStringBuilder.append(searchWordsQueryStringBuilder);
	resultStringBuilder.append(orderByPriceDESC);
	resultStringBuilder.append(limit);
	return resultStringBuilder.toString();
}

private static void appendStartingSubQuery(StringBuilder sb,Timestamp startFrom,boolean areSold){
	String query;
	if(startFrom == null){
		query = areSold ? absSearchSoldItemsQuery : absSearchForSaleItemsQuery;
	}else{
		query = areSold ? timeStartSearchSoldItemsQuery : timeStartSearchForSaleItemsQuery;
	}
	sb.append(query);
}

private static void injectKeyWords(List<String> searchKeyWords,PreparedStatement searchItemsPS,
		Timestamp startFrom,int pageNumber) throws SQLException{
	int injectedValuesCounter = 1,j;
	//for sale items
	for(int i = 0;i < UNIONS_NUMBER;i++){
		if(startFrom != null){
			searchItemsPS.setTimestamp(injectedValuesCounter++, startFrom);
		}
	for(String word : searchKeyWords){
		for(j = 0;j < DUPLICATE_STRING_NUMBER;j++){
		searchItemsPS.setString(injectedValuesCounter++, '%' + word + '%');
	}
	}
	//sold items
	}
	int offset = pageNumber * ITEMS_PER_PAGE;
	searchItemsPS.setInt(injectedValuesCounter++, offset);
	searchItemsPS.setInt(injectedValuesCounter++, offset + ITEMS_PER_PAGE);
}

/**
 * return if the item is for sale or not 
 */
public static boolean isForSaleItemStringQuery(long itemID){
	 PreparedStatement setSoldItemPS = null;
	 ResultSet rs = null;
	 Connection connection = null;
		try {
			connection = MyDBConnectionPool.getConnection();
			setSoldItemPS = connection.prepareStatement(isForSaleItemStringQuery);
			setSoldItemPS.setLong(1, itemID);
			rs = setSoldItemPS.executeQuery();
			return rs.first();
		}catch (SQLException e) {
		e.printStackTrace();
		}finally{
		try {
			if(setSoldItemPS != null)
				setSoldItemPS.close();
			if(rs != null)
			rs.close();
			if(connection != null)
				connection.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
}
		return false;
}

/**
 * 
 * @param itemID
 * @return the seller id of the item
 */
public static long getSellerID(long itemID){
	String sellerID = getField(itemID, getSellerIDSQLQuery);
	return (sellerID != null) ? Long.parseLong(sellerID) : -1;
}


public static double getCurrentBid(long itemID){
	String currentBid = getField(itemID, getCurrentBidQuery);
	return (currentBid != null) ? Double.parseDouble(currentBid) : -1;
}


public static long getWinnerID(long itemID){
	String numberStr = getField(itemID, getWinnerIDQuery);
	return (numberStr != null) ? Long.parseLong(numberStr) : -1;
}

// returns the query first object from result
//gets query string and injects the itemID to its first position
private static String getField(long itemID,String query){
	PreparedStatement getCurrentBidPS = null;
	 ResultSet rs = null;
	 Connection connection = null;
		try {
			connection = MyDBConnectionPool.getConnection();
			getCurrentBidPS = connection.prepareStatement(query);
			getCurrentBidPS.setLong(1, itemID);
			rs = getCurrentBidPS.executeQuery();
			 if(rs.first())
				return rs.getString(1);
		}catch (SQLException e) {
		e.printStackTrace();
		}finally{
		try {
			if(getCurrentBidPS != null)
				getCurrentBidPS.close();
			if(rs != null)
			rs.close();
			if(connection != null)
				connection.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
}
		return null;
}

}